﻿namespace SuperMarieApi.App.Controllers.Framework.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;

    public abstract class DataCommandBase
    {
        private DbHelper _dbHelper;
        protected DbCommand _dbCommand;
        internal DataCommandBase(string providerName, string connectionString)
        {
            this._dbHelper = new DbHelper(providerName, connectionString);
        }

        protected virtual DbCommand CreateDbCommand()
        {
            this._dbCommand = this._dbHelper.CreateDbCommand("", CommandType.Text, 1000);

            return this._dbCommand;
        }

        public string CommandText
        {
            get
            {
                return this._dbCommand.CommandText;
            }
            set
            {
                this._dbCommand.CommandText = value;
            }
        }

        #region Parameter Methods

        public object GetParameterValue(string name)
        {
            return this._dbCommand.Parameters[name].Value;
        }

        public void SetParameterValue(string name, object value)
        {
            if (value == null)
            {
                this._dbCommand.Parameters[name].Value = DBNull.Value;
            }
            else
            {
                this._dbCommand.Parameters[name].Value = value;
            }
        }

        public void ReplaceParameterValue(string placeHolder, string value)
        {
            this._dbCommand.CommandText = this._dbCommand.CommandText.Replace(placeHolder, value);
        }

        public void AddParameter(string name, DbType dbType, int size, ParameterDirection direction, object value)
        {
            this._dbHelper.CreateParameter(this._dbCommand, name, dbType, size, direction);
            this._dbCommand.Parameters[name].Value = value;
        }

        public void AddParameter(string name, DbType dbType, int size, ParameterDirection direction)
        {
            this.AddParameter(name, dbType, size, direction, null);
        }

        public void AddInParameter(string name, DbType dbType, int size, object value)
        {
            this.AddParameter(name, dbType, size, ParameterDirection.Input, value);
        }

        public void AddOutParameter(string name, DbType dbType, int size)
        {
            this.AddParameter(name, dbType, size, ParameterDirection.Output, DBNull.Value);
        }

        public void ClearParameters()
        {
            this._dbCommand.Parameters.Clear();
        }

        #endregion

        #region Execute Methods

        public int ExecuteNonQuery()
        {
            return this.Execute<int>(() =>
                {
                    return this._dbCommand.ExecuteNonQuery();
                });
        }

        public object ExecuteScalar()
        {
            return this.Execute<object>(() =>
            {
                return this._dbCommand.ExecuteScalar();
            });
        }

        public DbDataReader ExecuteReader()
        {
            return this.Execute<DbDataReader>(() =>
            {
                return this._dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }, false);
        }

        public DataSet ExecuteDataSet()
        {
            DataSet ds = new DataSet();
            DbDataAdapter da = this._dbHelper.CreateDataAdapter();
            da.SelectCommand = this._dbCommand;
            da.Fill(ds);

            return ds;
        }

        public T ExecuteEntity<T>() where T : new()
        {
            return this.Execute<T>(() =>
                {
                    var dataReader = this._dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    return EntityBuilder.BuildEntity<T>(dataReader);
                });
        }

        public List<T> ExecuteEntityList<T>() where T : new()
        {
            return this.Execute<List<T>>(() =>
            {
                var dataReader = this._dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
                return EntityBuilder.BuildEntityList<T>(dataReader);
            });
        }

        private T Execute<T>(Func<T> function, bool closeConnection = true)
        {
            try
            {
                this._dbCommand.Connection.Open();
                return function();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (closeConnection)
                {
                    this._dbCommand.Connection.Close();
                }
            }
        }

        #endregion
    }
}
